﻿using SAD_Assignment1.DTO;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace SAD_Assignment1.DAO
{
    public class PhotoDAO
    {
        public List<PhotoDTO> getPhotoList(int page, int maxPhoto, int photoType)
        {
            List<PhotoDTO> list = new List<PhotoDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getPhotoList(@page, @maxPhoto, @photoType)";
                    cmd.Parameters.AddWithValue("@page", page);
                    cmd.Parameters.AddWithValue("@maxPhoto", maxPhoto);
                    cmd.Parameters.AddWithValue("@photoType", photoType);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach(DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string url = row["img_url"].ToString();
                        string title = row["title"].ToString();
                        DateTime postDate = DateTime.Parse(row["post_date"].ToString());
                        int accID = (int)row["acc_ID"];
                        string accName = row["name"].ToString();
                        //string type = row["type_name"].ToString();
                        PhotoDTO photo = new PhotoDTO(ID,url,title,postDate,"",accID,accName);
                        list.Add(photo);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public List<PhotoDTO> getPhoto(int photoID)
        {
            List<PhotoDTO> list = new List<PhotoDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getPhoto(@photoID)";                    
                    cmd.Parameters.AddWithValue("@photoID", photoID);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string url = row["img_url"].ToString();
                        string title = row["title"].ToString();
                        DateTime postDate = DateTime.Parse(row["post_date"].ToString());
                        int accID = (int)row["acc_ID"];
                        string accName = row["name"].ToString();
                        string type = row["type_name"].ToString();
                        PhotoDTO photo = new PhotoDTO(ID, url, title, postDate, type, accID, accName);

                        list.Add(photo);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public bool AddNewPhoto(string title, string url, string email)
        {
            if (title.Length > 250)
            {
                title = title.Substring(0, 250);
            }
            if (url.Length > 250) { return false; }
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "addNewPhoto";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@title", title);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@img_url", url);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

        public bool ApprovePhoto(int photoID)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "ApprovePhoto";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@photoID", photoID);                    
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        public bool DeletePhoto(int photoID)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "DeletePhoto";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@photoID", photoID);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

    }
}